package zhuzx.temp;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.zhuzx.util.ExcelUtil;
import org.zhuzx.util.FileOperationUtil;
import org.zhuzx.util.GeneralUtil;
import org.zhuzx.util.JdbcUtil;
import org.zhuzx.util.ListDataUtil;
import org.zhuzx.util.WebUtil;

public class HxCrmDeliveryCounter {
	
	public static void main(String[] args) {
		String sql = "select substr(create_date, 1, 7) as month,\n" +
				"count(*) as nums,\n" + 
				"sum(nvl(PICK_UP_AMOUNT,DELIVER_AMOUNT)) as amount\n" + 
				"from OA.Crm_Dl_Delivery t where t.CREATE_DATE > '2019-01-01 00:00:00' "
				+ "and t.CREATE_DATE < '2022-02-01 00:00:00'\n" + 
				"and DELIVERY_TYPE = 'ZLF1'\n" + 
				"group by substr(create_date, 1, 7)\n" + 
				"order by month";
		System.out.println(sql);
		List<Map<String,Object>> list = JdbcUtil.executeQueryForObject(sql);
		ListDataUtil.modifyKeyNameToLowerCase(list, true);
		List<Map<String,Object>> yoy = new ArrayList<>();//对比一年前同月份数据
		for (int i=12; i<list.size(); i++) {
			Map<String,Object> rowCur = list.get(i);
			Map<String,Object> rowPre = list.get(i-12);
			String monthKey = rowCur.get("month").toString();
			if (!monthKey.substring(5).endsWith(
					rowPre.get("month").toString().substring(5))) {
				throw new RuntimeException("error");
			}
			Map<String, Object> result = new LinkedHashMap<>();
			BigDecimal countDif = GeneralUtil.arithmeticOperate(
					rowCur.get("nums"), '-', rowPre.get("nums"));
			BigDecimal amountDif = GeneralUtil.arithmeticOperate(
					rowCur.get("amount"), '-', rowPre.get("amount"));
			result.put("当前月", monthKey);
			result.put("单量对比一年前同月份", countDif);
			result.put("吨数对比一年前同月份", amountDif);
			yoy.add(result);
		}
		boolean[][] isNumCell = {{false, true, true}};
		FileOperationUtil.writeToFile(ExcelUtil.createExcel(
				true, null, isNumCell, WebUtil.createStringTypeCopy(yoy)), "对比一年前同月份.xlsx", true);
		
		List<Map<String,Object>> mToPm = new ArrayList<>();
		for (int i=1; i<list.size(); i++) {
			Map<String,Object> rowCur = list.get(i);
			Map<String,Object> rowPre = list.get(i-1);
			String monthKey = rowCur.get("month").toString();
			Map<String, Object> result = new LinkedHashMap<>();
			BigDecimal countDif = GeneralUtil.arithmeticOperate(
					rowCur.get("nums"), '-', rowPre.get("nums"));
			BigDecimal amountDif = GeneralUtil.arithmeticOperate(
					rowCur.get("amount"), '-', rowPre.get("amount"));
			result.put("当前月", monthKey);
			result.put("单量对比上月份", countDif);
			result.put("吨数对比上月份", amountDif);
			mToPm.add(result);
		}
		FileOperationUtil.writeToFile(ExcelUtil.createExcel(
				true, null, isNumCell, WebUtil.createStringTypeCopy(mToPm)), "对比上月份.xlsx", true);
	}

}